package com.jcl.core.db;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName DbConnection
 * @Description 使用 query, insert, update, delete 完成后 一定要 关闭连接 closeConnection()
 * @Author yyliu
 * @Date 2018/12/14 10:07
 * @Version 1.0
 **/
public class DbConnection {

    private static final Logger logger = LoggerFactory.getLogger(DbConnection.class);

    public List<Map<String, Object>> query(String sql) {
        List<Map<String, Object>> dataList = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            dataList = executeQuery(connection, preparedStatement);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
        return dataList;
    }

    public int insert(String sql) {
        connection = getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);
            return executeUpdate(connection, preparedStatement);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
        return 0;
    }

    public int update(String sql) {
        connection = getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);
            return executeUpdate(connection, preparedStatement);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
        return 0;
    }

    public int delete(String sql) {
        connection = getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);
            return executeUpdate(connection, preparedStatement);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
        return 0;
    }

    private List<Map<String, Object>> executeQuery(Connection connection,
                                                   PreparedStatement preparedStatement) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        ResultSet rs = null;
        try {
            rs = preparedStatement.executeQuery();
            dataList = contractResultSet(rs);
            if (!isAutoCommit) {
                connection.commit(); // 手动 提交事务
            }
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                logger.error(e1.getMessage(), e1);
            }
            logger.error(e.getMessage(), e);
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != preparedStatement) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
        return dataList;
    }

    private int executeUpdate(Connection connection,
                              PreparedStatement preparedStatement) {
        int count = 0;
        try {
            count = preparedStatement.executeUpdate();
            if (!isAutoCommit) {
                connection.commit(); // 手动 提交事务
            }
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                logger.error(e1.getMessage(), e1);
            }
            logger.error(e.getMessage(), e);
        } finally {
            try {
                if (null != preparedStatement) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
        return count;
    }

    public Connection getConnection() {
        if (null != connection) {
            return connection;
        }
        try {
            if (!isAutoCommit) {
                connection.setAutoCommit(isAutoCommit);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
        return connection;
    }

    public void closeConnection() {
        if (null != connection) {
            try {
                connection.close();
            } catch (SQLException e) {
                logger.error(e.getMessage(), e);
            }
        }
    }

    private List<Map<String, Object>> contractResultSet(ResultSet rs) throws SQLException {
        List<Map<String, Object>> dataList = new ArrayList<>();
        ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
        int columnCount = md.getColumnCount();   //获得列数
        while (rs.next()) {
            Map<String, Object> rowData = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            dataList.add(rowData);
        }
        return dataList;
    }

    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private boolean isAutoCommit = true;

    public DbConnection() {
    }

    public DbConnection(Connection connection) {
        this.connection = connection;
    }

    public void setAutoCommit(boolean autoCommit) {
        isAutoCommit = autoCommit;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public Connection setConnection(String driverClassName, String url, String user, String password) {
        // 定义连接
        try {
            // 加载驱动
            Class.forName(driverClassName);
            connection = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return connection;
    }
}
